package ut.data;
import ut.util.Cache;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.naming.NamingException;
public class Profile{
	public static Profile getProfile(int id){
		Profile profile;
		if((profile = Cache.getProfile(id)) != null){
			return profile;
		}
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{

			con = Database.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery("select * from profile where id=" + id);
			if(rs.next()){
				profile = new Profile(rs.getString("description"), rs.getString("education"), rs.getString("certification"), rs.getString("experience"));
			}else{
				return null;
			}
			rs = stmt.executeQuery("select count(*) from specialty where user=" + id);
			rs.next();
			profile.specialties = new int[rs.getInt("count(*)")][2];
			rs = stmt.executeQuery("select from_lan,to_lan from specialty where user=" + id);
			int index = 0;
			while(rs.next()){
				profile.specialties[index][0] = rs.getInt("from_lan");
				profile.specialties[index][1] = rs.getInt("to_lan");
				index++;
			}
			// 加入快取
			Cache.cacheProfile(id, profile);
			return profile;
		}catch(NamingException e){
			e.printStackTrace();
			return null;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return null;
			}
		}
	}
	public static boolean createProfile(int id, String description, String education, String certification, String experience, String[] from, String[] to){
		String[] queries = new String[from.length + 2];
		queries[0] = "insert ignore into profile(id,description,education,certification,experience) values(" + id + ",'" + description.replace("'", "''") + "','" + education.replace("'", "''") + "','" + certification.replace("'", "''") + "','" + experience.replace("'", "''") + "')";
		queries[1] = "delete from specialty where user=" + id;
		for(int i = 0; i < from.length; i++){
			queries[i+2] = "insert into specialty values(" + id + "," + from[i] + "," + to[i] + ")";
		}
		return Database.executeTransaction(queries);
	}
	public static boolean modifyProfile(int id, String description, String education, String certification, String experience, String[] from, String[] to){
		String[] queries = new String[from.length + 2];
		queries[0] = "update profile set description='" + description.replace("'", "''") + "',education='" + education.replace("'", "''") + "',certification='" + certification.replace("'", "''") + "',experience='" + experience.replace("'", "''") + "' where id=" + id;
		queries[1] = "delete from specialty where user=" + id;
		for(int i = 0; i < from.length; i++){
			queries[i+2] = "insert into specialty values(" + id + "," + from[i] + "," + to[i] + ")";
		}
		// 刪除快取
		Cache.removeProfile(id);
		return Database.executeTransaction(queries);
	}
	/* Instance Definition */
	public String description;
	public String education;
	public String certification;
	public String experience;
	public int[][] specialties;
	public Profile(String description, String education, String certification, String experience){
		this.description = description;
		this.education = education;
		this.certification = certification;
		this.experience = experience;
	}
}